﻿/****** Object:  Database [INVEST]    Script Date: 01/16/2015 15:51:11 ******/
CREATE DATABASE [INVEST] ON  PRIMARY 
( NAME = N'INVEST', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2008R2\MSSQL\DATA\INVEST.mdf' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
 LOG ON 
( NAME = N'INVEST_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2008R2\MSSQL\DATA\INVEST_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO
ALTER DATABASE [INVEST] SET COMPATIBILITY_LEVEL = 100
GO
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [INVEST].[dbo].[sp_fulltext_database] @action = 'enable'
end
GO
ALTER DATABASE [INVEST] SET ANSI_NULL_DEFAULT OFF
GO
ALTER DATABASE [INVEST] SET ANSI_NULLS OFF
GO
ALTER DATABASE [INVEST] SET ANSI_PADDING OFF
GO
ALTER DATABASE [INVEST] SET ANSI_WARNINGS OFF
GO
ALTER DATABASE [INVEST] SET ARITHABORT OFF
GO
ALTER DATABASE [INVEST] SET AUTO_CLOSE OFF
GO
ALTER DATABASE [INVEST] SET AUTO_CREATE_STATISTICS ON
GO
ALTER DATABASE [INVEST] SET AUTO_SHRINK OFF
GO
ALTER DATABASE [INVEST] SET AUTO_UPDATE_STATISTICS ON
GO
ALTER DATABASE [INVEST] SET CURSOR_CLOSE_ON_COMMIT OFF
GO
ALTER DATABASE [INVEST] SET CURSOR_DEFAULT  GLOBAL
GO
ALTER DATABASE [INVEST] SET CONCAT_NULL_YIELDS_NULL OFF
GO
ALTER DATABASE [INVEST] SET NUMERIC_ROUNDABORT OFF
GO
ALTER DATABASE [INVEST] SET QUOTED_IDENTIFIER OFF
GO
ALTER DATABASE [INVEST] SET RECURSIVE_TRIGGERS OFF
GO
ALTER DATABASE [INVEST] SET  DISABLE_BROKER
GO
ALTER DATABASE [INVEST] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
GO
ALTER DATABASE [INVEST] SET DATE_CORRELATION_OPTIMIZATION OFF
GO
ALTER DATABASE [INVEST] SET TRUSTWORTHY OFF
GO
ALTER DATABASE [INVEST] SET ALLOW_SNAPSHOT_ISOLATION OFF
GO
ALTER DATABASE [INVEST] SET PARAMETERIZATION SIMPLE
GO
ALTER DATABASE [INVEST] SET READ_COMMITTED_SNAPSHOT OFF
GO
ALTER DATABASE [INVEST] SET HONOR_BROKER_PRIORITY OFF
GO
ALTER DATABASE [INVEST] SET  READ_WRITE
GO
ALTER DATABASE [INVEST] SET RECOVERY FULL
GO
ALTER DATABASE [INVEST] SET  MULTI_USER
GO
ALTER DATABASE [INVEST] SET PAGE_VERIFY CHECKSUM
GO
ALTER DATABASE [INVEST] SET DB_CHAINING OFF
GO
EXEC sys.sp_db_vardecimal_storage_format N'INVEST', N'ON'
GO
USE [INVEST]
GO
/****** Object:  Table [dbo].[project_task_attendee]    Script Date: 01/16/2015 15:51:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[project_task_attendee](
	[project_code] [char](9) NOT NULL,
	[workflow_id] [char](2) NOT NULL,
	[task_id] [varchar](50) NOT NULL,
	[role_id] [char](2) NOT NULL,
	[attend_person] [nvarchar](10) NOT NULL,
	[task_status] [nvarchar](10) NULL,
	[create_person] [nvarchar](10) NULL,
	[create_date] [datetime] NULL,
	[end_time] [datetime] NULL,
	[Consigner] [nvarchar](10) NULL,
	[previous_task_id] [nvarchar](50) NULL,
	[previous_task_attendee] [nvarchar](10) NULL,
 CONSTRAINT [PK_project_task_attendee] PRIMARY KEY CLUSTERED 
(
	[project_code] ASC,
	[workflow_id] ASC,
	[task_id] ASC,
	[role_id] ASC,
	[attend_person] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[project_task]    Script Date: 01/16/2015 15:51:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[project_task](
	[project_code] [char](9) NOT NULL,
	[workflow_id] [char](2) NOT NULL,
	[task_id] [varchar](50) NOT NULL,
	[project_phase] [nvarchar](10) NULL,
	[project_status] [nvarchar](10) NULL,
	[sequence] [int] NULL,
	[task_name] [nvarchar](30) NULL,
	[task_type] [nvarchar](10) NULL,
	[apply_tool] [nvarchar](50) NULL,
	[parameters] [nvarchar](20) NULL,
	[duration] [int] NULL,
	[merge_relation] [varchar](5) NULL,
	[pause_start_time] [datetime] NULL,
	[pause_end_time] [datetime] NULL,
	[flow_tool] [nvarchar](50) NULL,
	[create_person] [nvarchar](10) NULL,
	[create_date] [datetime] NULL,
	[start_time] [datetime] NULL,
	[start_mode] [char](6) NULL,
	[hasMessage] [bit] NULL,
 CONSTRAINT [PK_project_task] PRIMARY KEY CLUSTERED 
(
	[project_code] ASC,
	[workflow_id] ASC,
	[task_id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[project]    Script Date: 01/16/2015 15:51:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[project](
	[project_code] [char](9) NOT NULL,
	[corporation_code] [char](5) NOT NULL,
	[phase] [nvarchar](10) NULL,
	[status] [nvarchar](10) NULL,
	[apply_service_type] [nvarchar](20) NULL,
	[apply_bank] [char](2) NULL,
	[apply_branch_bank] [char](2) NULL,
	[apply_sum] [numeric](15, 2) NULL,
	[apply_term] [int] NULL,
	[purpose] [nvarchar](255) NULL,
	[apply_date] [datetime] NULL,
	[recommend_type] [nvarchar](10) NULL,
	[recommend_person] [nvarchar](30) NULL,
	[is_first_loan] [bit] NULL,
	[is_again_apply] [bit] NULL,
	[consult_serial_num] [int] NULL,
	[create_person] [nvarchar](10) NULL,
	[create_date] [datetime] NULL,
	[isliving] [bit] NOT NULL,
	[guarantee_letter_type] [varchar](25) NULL,
	[reimburse_type] [varchar](25) NULL,
	[beneficiary] [nvarchar](50) NULL,
	[workflow] [nvarchar](20) NULL,
	[bh_project_name] [nvarchar](50) NULL,
	[bh_project_content] [nvarchar](255) NULL,
	[bh_implement_ability] [nvarchar](255) NULL,
	[bh_counterclaim_condition] [nvarchar](255) NULL,
	[bh_beneficiary_introduction] [nvarchar](255) NULL,
	[is_accept] [bit] NOT NULL,
	[APD] [datetime] NULL,
	[applicantTeam_name] [nvarchar](20) NULL,
	[managerTeam_name] [nvarchar](20) NULL,
	[guarant_start_date] [datetime] NULL,
	[guarant_end_date] [datetime] NULL,
	[guarantee_rate] [numeric](9, 2) NULL,
	[security_rate] [numeric](9, 2) NULL,
	[origPhase] [nvarchar](10) NULL,
	[origStatus] [nvarchar](10) NULL,
	[is_refee] [bit] NULL,
	[refee_date] [datetime] NULL,
	[bh_project_district_parent_name] [nvarchar](50) NULL,
	[bh_project_district_name] [nvarchar](50) NULL,
	[split_workflow_id] [char](2) NULL,
	[jbDate] [char](10) NULL,
	[fkDate] [char](10) NULL,
 CONSTRAINT [PK_project] PRIMARY KEY CLUSTERED 
(
	[project_code] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[workflow]    Script Date: 01/16/2015 15:51:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[workflow](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[workflow_id] [char](2) NOT NULL,
	[service_type] [nvarchar](50) NOT NULL,
	[version] [nvarchar](10) NULL,
	[service_type_parent] [nvarchar](20) NULL,
	[phase] [nvarchar](10) NULL,
	[service_type_parent_order] [int] NULL,
	[service_type_order] [int] NULL,
	[IsDisplay] [bit] NULL,
 CONSTRAINT [PK_workflow] PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[work_log]    Script Date: 01/16/2015 15:51:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[work_log](
	[serial_num] [bigint] IDENTITY(1,1) NOT NULL,
	[project_code] [char](9) NULL,
	[task_id] [nvarchar](50) NULL,
	[role_id] [char](2) NULL,
	[task_name] [nvarchar](30) NULL,
	[attend_person] [nvarchar](10) NULL,
	[task_status] [nvarchar](10) NULL,
	[start_time] [datetime] NULL,
	[finish_time] [datetime] NULL,
	[day_name] [nvarchar](10) NULL,
	[period] [nvarchar](10) NULL,
	[description] [nvarchar](150) NULL,
	[auto] [bit] NULL,
	[project_phase] [nvarchar](10) NULL,
	[project_status] [nvarchar](10) NULL,
	[create_date] [datetime] NULL,
	[subtask_name] [nvarchar](50) NULL,
	[start_mode] [char](6) NULL,
	[post_name] [nvarchar](20) NULL,
	[man_hour] [numeric](9, 2) NULL,
	[log_number] [bigint] NULL,
	[abc] [nchar](10) NULL,
 CONSTRAINT [PK_work_log] PRIMARY KEY CLUSTERED 
(
	[serial_num] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[task_transfer_template]    Script Date: 01/16/2015 15:51:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[task_transfer_template](
	[workflow_id] [char](2) NOT NULL,
	[task_id] [varchar](50) NOT NULL,
	[next_task] [varchar](50) NOT NULL,
	[transfer_condition] [nvarchar](50) NOT NULL,
	[status] [nchar](12) NULL,
	[isItem] [smallint] NULL,
 CONSTRAINT [PK_task_transfer_template] PRIMARY KEY CLUSTERED 
(
	[workflow_id] ASC,
	[task_id] ASC,
	[next_task] ASC,
	[transfer_condition] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[task_template]    Script Date: 01/16/2015 15:51:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[task_template](
	[workflow_id] [char](2) NOT NULL,
	[task_id] [varchar](50) NOT NULL,
	[task_id_1] [char](2) NULL,
	[phase] [nchar](10) NULL,
	[status] [nchar](10) NULL,
	[sequence] [int] NULL,
	[task_name] [nvarchar](30) NULL,
	[task_type] [nvarchar](10) NULL,
	[apply_tool] [nvarchar](50) NULL,
	[parameters] [nvarchar](20) NULL,
	[duration] [int] NULL,
	[merge_relation] [varchar](5) NULL,
	[flow_tool] [nvarchar](50) NULL,
	[create_person] [nvarchar](10) NULL,
	[create_date] [datetime] NULL,
	[start_mode] [char](6) NULL,
	[hasMessage] [bit] NULL,
 CONSTRAINT [PK_task_template] PRIMARY KEY CLUSTERED 
(
	[workflow_id] ASC,
	[task_id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[task_role_template]    Script Date: 01/16/2015 15:51:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[task_role_template](
	[workflow_id] [char](2) NOT NULL,
	[task_id] [varchar](50) NOT NULL,
	[role_id] [char](2) NOT NULL,
	[task_id_1] [char](2) NULL,
 CONSTRAINT [PK_task_role_template] PRIMARY KEY CLUSTERED 
(
	[workflow_id] ASC,
	[task_id] ASC,
	[role_id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object:  Default [DF_project_isliving]    Script Date: 01/16/2015 15:51:11 ******/
ALTER TABLE [dbo].[project] ADD  CONSTRAINT [DF_project_isliving]  DEFAULT ((1)) FOR [isliving]
GO
/****** Object:  Default [DF__project__is_acce__1367E606]    Script Date: 01/16/2015 15:51:11 ******/
ALTER TABLE [dbo].[project] ADD  DEFAULT ((0)) FOR [is_accept]
GO
/****** Object:  Default [DF__project__is_refe__145C0A3F]    Script Date: 01/16/2015 15:51:11 ******/
ALTER TABLE [dbo].[project] ADD  DEFAULT ((0)) FOR [is_refee]
GO
/****** Object:  ForeignKey [FK_task_role_template_task_template]    Script Date: 01/16/2015 15:51:11 ******/
ALTER TABLE [dbo].[task_role_template]  WITH NOCHECK ADD  CONSTRAINT [FK_task_role_template_task_template] FOREIGN KEY([workflow_id], [task_id])
REFERENCES [dbo].[task_template] ([workflow_id], [task_id])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[task_role_template] CHECK CONSTRAINT [FK_task_role_template_task_template]
GO
